import tkinter as tk
from tkinter import scrolledtext
import pymssql

def open(s):
    window = tk.Tk()
    window.title("养护人员-" + s + "---增加")
    window.geometry("600x300")
#设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)

    label1 = tk.Label(window, text="请输入您的姓名：")
    entry1 = tk.Entry(window)
    label2 = tk.Label(window, text="请输入您的要完成的养护任务编号：")
    entry2 = tk.Entry(window)
    label3 = tk.Label(window, text="请输入您的要完成的养护任务对象：")
    entry3 = tk.Entry(window)
    label4 = tk.Label(window, text="请输入您的要完成的养护任务执行时间：")
    entry4 = tk.Entry(window)

    conn = pymssql.connect(host='localhost', server='LAPTOP-QBHKFR9T', port='1433', user='Mysql',
                           password='15681743536',
                           database='植物管理系统', charset='utf8')

    if conn:
        print("连接成功!")

    def check1():
        message = entry1.get()

        cursor = conn.cursor() #获取游标
        try:
            sql = f"SELECT * FROM 操作 WHERE 操作.执行人员 = '{message}'"
            cursor.execute(sql)
            result = ""
            if cursor.rowcount == 0:
                scrollbar.insert(tk.INSERT, "没有需要" + message + "的养护任务！")
            else:
                for row in cursor:  # 遍历输出
                    result += "任务编号：" + "%s" % (row[0]) + "  " + "任务名称：" + "%s" % (
                        row[1]) + "  " + "任务类型：" + "%s" % (row[2]) + "  " + "执行人员：" + "%s" % (
                                  row[3]) + "  " + "发布时间：" + "%s" % (
                              row[4]) + "  " + "任务状态：" + "%s" % (
                                  row[5]) + "\n\n"
                scrollbar.insert(tk.INSERT, "需要" + message + "执行的养护任务有：\n" + result)

        except Exception as e:
            scrollbar.insert(tk.INSERT, "查询失败，原因如下：" + str(e))
            conn.rollback()

    def check_message():
        message1 = entry2.get()
        name = entry1.get()
        plant_name=entry3.get()
        time=entry4.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT 任务名称 FROM 操作 WHERE 操作.任务编号 = '{message1}'"
        cursor.execute(sql)
        result1 = ""

        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该养护任务信息"
        else:
            for row in cursor:
                result1+=row[0]

            cursor = conn.cursor()  # 获取游标
            sql = f"SELECT 县名称,市名称,省名称 FROM 基本信息,分布,分布区域 WHERE 基本信息.植物编号=分布.植物编号 AND 分布.县编号=分布区域.县编号 AND 基本信息.植物名称 = '{plant_name}'"
            cursor.execute(sql)
            result2 = ""
            if cursor.rowcount == 0:  # 如果没有找到记录
                result = "无该植物的分布信息，无法找到监测地点"
            else:
                for row in cursor:
                    result2 += "%s,%s,%s" % (row[0], row[1], row[2])
                cursor = conn.cursor()  # 获取游标
                sql = f"SELECT 植物病名 FROM 基本信息 WHERE 植物名称 = '{plant_name}'"
                cursor.execute(sql)
                result21= ""
                if cursor.rowcount == 0:  # 如果没有找到记录
                    result = "无该植物的病虫害相关信息"
                else:
                    for row in cursor:
                        result21 += row[0]
                    cursor = conn.cursor()  # 获取游标
                    sql = f"SELECT 病虫害名称,防治方式,药剂名称,药剂用量,作用期限 FROM 病虫害防治 WHERE 病虫害名称 = '{result21}'"
                    cursor.execute(sql)
                    result3 = ""
                    if cursor.rowcount == 0:  # 如果没有找到记录
                        result = "无该植物的病虫害相关信息"
                    else:
                        for row in cursor:
                            result3 += "%s,%s,%s,%s,%s" % (row[0], row[1], row[2], row[3], row[4])
                        cursor = conn.cursor()  # 获取游标
                        sql = f" INSERT INTO 养护(养护任务编号, 养护任务名称, 养护任务执行地点, 养护任务执行时间, 养护任务执行人员, 养护任务描述, 养护对象, 养护任务状态) VALUES('{message1}', '{result1}', '{result2}', '{time}', '{name}', '{result3}', '{plant_name}', '0')"
                        cursor.execute(sql)
                        result3 = ""
                        if cursor.rowcount == 0:  # 如果没有找到记录
                            result = "无该植物的病虫害相关信息"
                        else:
                            for row in cursor:
                                result3 += "%s,%s,%s,%s,%s" % (row[0], row[1], row[3], row[4], row[5])
                        conn.commit()  # 提交事务，使更改生效
                        scrollbar.delete(1.0, tk.END)
                        scrollbar.insert(tk.INSERT, "养护任务添加完成")

    button1 = tk.Button(window, text="查询操作表", command=lambda: check1(), width=20, height=1)
    button2 = tk.Button(window, text="添加养护任务", command=lambda: check_message(),width=20, height=1)
    return_button = tk.Button(window, text="返回", command=window.destroy, width=20, height=1)

    label1.grid(row=0, column=0)
    entry1.grid(row=0, column=1)
    label2.grid(row=0, column=2)
    entry2.grid(row=0, column=3)
    label3.grid(row=1, column=0)
    entry3.grid(row=1, column=1)
    label4.grid(row=1, column=2)
    entry4.grid(row=1, column=3)

    button1.grid(row=2, column=0)
    button2.grid(row=2,column=1)
    return_button.grid(row=3, column=1, sticky="nsew")
    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=5, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass